# %%
# 初始化
import pandas as pd

import reader
from common import *

(year, month), _ = year_month()

FP_GONGDAN_DANGYUE = (
    f"data/0-工单/家客工单导出_潮州_{year}-{month:02}.zip"
)
FP_BANGDING = (
    f"data/7-智能安防/{year}{month:02}_智能安防绑定明细清单.xlsx"
)
FP_ZHIJIAN = (
    f"data/7-智能安防/{year}{month:02}_智能安防人工质检不合格清单.xlsx"
)

# %%
# 读取工单
usecols = (
    "工单号",
    "CRM工单号",
    "CRM业务流水号",
    "地市",
    "派单人",
    "操作类型",
    "是否施工标识",
    "宽带帐号",
    "产品名称",
    "派单日期",
    "结单时间",
    "工单状态",
    "装维组/用户班",
    "装维人员登录账号",
    "客户类型",
    "五级地址ID",
    "产品名称1",
    "设备CMEI码1",
)
df = reader.read_gongdan(FP_GONGDAN_DANGYUE, usecols)

# %%
# 筛选数据
# 操作类型 = 业务开通，
# 是否施工 = 普通开通施工
# 产品名称 = 智慧管家基础安装服务
# 工单状态 = 归档
mask = (
    (df.操作类型 == "业务开通")
    & (df.是否施工标识 == "普通开通施工")
    & (df.产品名称 == "智慧管家基础安装服务")
    & (df.工单状态 == "归档")
)
df = df[mask]

# %%
# 录入设备的串码信息（CMEI/IMEI码）核查
df["录入设备的串码信息（CMEI/IMEI码）核查"] = S_NOT_PASSED
mask = df["设备CMEI码1"].str.startswith("111") & (
    df["设备CMEI码1"].str.len() == 15
)
df.loc[mask, "录入设备的串码信息（CMEI/IMEI码）核查"] = S_PASSED

# %%
# 绑定和家亲核查
df_bangding = pd.read_excel(
    FP_BANGDING,
    usecols=[
        "CMEI",
        "设备型号",
        "订购状态",
        "设备绑定状态",
    ],
    dtype="string",
)
# df_bangding.CMEI = df_bangding.CMEI
df["绑定和家亲核查"] = S_NOT_PASSED
mask = df["设备CMEI码1"].isin(
    df_bangding.loc[df_bangding.设备绑定状态 == "绑定", "CMEI"]
)
df.loc[mask, "绑定和家亲核查"] = S_PASSED

# %%
# 当月账号是否重复核查
df["当月账号是否重复核查"] = S_NOT_PASSED
tmp = df.groupby(["宽带帐号", "设备CMEI码1"]).filter(
    lambda x: len(x) > 1
)
df.loc[~df.宽带帐号.isin(tmp.宽带帐号), "当月账号是否重复核查"] = S_PASSED

tmp["order"] = 0
tmp.loc[tmp.产品名称1.str.find("摄像头") != -1, "order"] = 1
tmp.loc[tmp.产品名称1.str.find("安装服务") != -1, "order"] = 2
tmp.loc[tmp.产品名称1.str.find("配送") != -1, "order"] = 3
tmp.sort_values(by=["宽带帐号", "order"], inplace=True)
tmp.drop_duplicates(
    subset=["宽带帐号"], keep="first", inplace=True
)
df.loc[tmp.index, "当月账号是否重复核查"] = S_PASSED

# %%
# 录入cmei码是否重复核查
df_cmei_dup = df.groupby("设备CMEI码1").filter(
    lambda x: len(x) > 1
)["设备CMEI码1"]
df["录入cmei码是否重复核查"] = S_PASSED
df.loc[
    df["设备CMEI码1"].isin(df_cmei_dup), "录入cmei码是否重复核查"
] = S_NOT_PASSED
df.loc[
    df.当月账号是否重复核查 == S_NOT_PASSED, "录入cmei码是否重复核查"
] = S_BYPASSED
df.loc[tmp.index, "录入cmei码是否重复核查"] = S_PASSED


# %%
# 质检核查
df_zhijian = pd.read_excel(
    FP_ZHIJIAN,
    usecols=["宽带帐号"],
    dtype="string",
)
df["质检核查"] = S_PASSED
df.loc[
    df["宽带帐号"].isin(df_zhijian["宽带帐号"]), "质检核查"
] = S_NOT_PASSED

# %%
# 是否扣费成功(订购)
# 绑定明细中是否“订购”状态，是则通过
df["是否扣费成功(订购)"] = S_NOT_PASSED
mask = df["设备CMEI码1"].isin(
    df_bangding.loc[df_bangding["订购状态"] == "订购", "CMEI"]
)
df.loc[mask, "是否扣费成功(订购)"] = S_PASSED

# %%
# 设备型号
df = df.merge(
    df_bangding[["CMEI", "设备型号"]],
    left_on="设备CMEI码1",
    right_on="CMEI",
    how="left",
)
df.drop("CMEI", axis=1, inplace=True)

# %%
# 业务结算类型
df["业务结算类型"] = "普通版"
df.loc[
    df["设备型号"].isin(
        [
            "G30P",
            "LYC20",
            "CM3TPA",
            "TC61",
        ]
    ),
    "业务结算类型",
] = "平安乡村"

# %%
# 地市稽核结果
CHECK_COLUMNS = [
    "录入设备的串码信息（CMEI/IMEI码）核查",
    "绑定和家亲核查",
    "当月账号是否重复核查",
    "录入cmei码是否重复核查",
    "质检核查",
    "是否扣费成功(订购)",
]
not_passed = (df[CHECK_COLUMNS] == S_NOT_PASSED).any(axis=1)
df["地市稽核结果"] = S_PASSED
df.loc[not_passed, "地市稽核结果"] = S_NOT_PASSED

# 是否结算
df["是否结算"] = S_YES
df.loc[not_passed, "是否结算"] = S_NO

# %%
# 安防工单地市稽核结果
df.to_excel(
    f"report/7-智能安防/{year}{month:02}_安防工单地市稽核结果.xlsx",
    index=False,
)

# %%
# 完成
